﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;

namespace others
{
    public class demo_getdb_structure_toexcel
    {
        private IList<string> nochecktables = new List<string>
        {
            "rptdb_child_oasis_2406",
            "rptdb_jobmaster",
            "rptdb_tempchild_backup",
            "rptdb_tempchild_backup_2406",
            "rptdb_tempproducts_backup",
            "rptdb_SynchronizeJobLog",
            "rptdb_Products_oasis",
            "rptdb_Error",
            "sysdiagrams"
        };

        private readonly string SQLTABLES = "select name from sysobjects where xtype = 'u'";
        private readonly string SQLCOLUMNS = "select name from syscolumns where id=object_id(N'{0}')";

        private readonly string SQLTOGETHER = @"SELECT c.name as columnname,o.name as tablename 
                                                  FROM syscolumns AS c 
                                                  INNER JOIN sysobjects AS o ON c.id = o.id 
                                                  where o.type = 'u'
                                                    and o.name not in ('sysdiagrams','rptdb_Error','rptdb_Products_oasis','rptdb_SynchronizeJobLog','rptdb_child_oasis_2406','rptdb_jobmaster','rptdb_tempchild_backup','rptdb_tempchild_backup_2406','rptdb_tempproducts_backup')
                                                 order by o.name";

        private readonly string STRDBCONNECTION = @"Data Source=FRGOPSQLGL06\INSGLOB06; Initial Catalog=mdmreportdb; User Id=mdmreportdb_pro_db_author; Password=Bratis27!;Connection Timeout=60;";

        private readonly string SQLLENTH = "select max(len([{0}])) as length from {1}";

        public void Get()
        {
            IDictionary<string, string> sqlgetlength = new Dictionary<string, string>();

            using (SqlConnection conn = new SqlConnection(STRDBCONNECTION))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQLTOGETHER, conn);
                SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                string column = string.Empty;
                string table = string.Empty;
                while (dr.Read())
                {
                    column = dr.GetString(0);
                    table = dr.GetString(1);

                    if (!column.EndsWith("ID"))
                    {
                        sqlgetlength.Add(table + "," + column, string.Format(SQLLENTH, column, table));
                    }
                }
            }

            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("row");
            dt.Columns.Add("sql");
            dt.Columns.Add("tablename");
            dt.Columns.Add("columnname");
            System.Data.DataRow datar = null;

            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("sql.xls", SpreadsheetDocumentType.Workbook);
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet();
            SheetData data = new SheetData();

            using (SqlConnection conn = new SqlConnection(STRDBCONNECTION))
            {
                SqlCommand cmd = null;
                SqlDataReader dr = null;
                int length = 0;
                //StreamWriter sw = File.CreateText("sql.txt");               

                foreach (var item in sqlgetlength)
                {
                    conn.Open();
                    using (cmd = new SqlCommand(item.Value, conn))
                    {
                        using (dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                if (!dr.IsDBNull(0))
                                {
                                    length = int.Parse(dr[0].ToString());

                                    datar = dt.NewRow();
                                    datar[0] = length;
                                    datar[1] = item.Value;
                                    datar[2] = item.Key.Split(',')[0];
                                    datar[3] = item.Key.Split(',')[1];
                                    dt.Rows.Add(datar);

                                    //sw.WriteLine(length + "  " + item.Value);

                                    if (length == 100)
                                    {
                                        Console.WriteLine(string.Format("table name is {0} and columname is {1}", item.Key.Split(',')[0], item.Key.Split(',')[1]));
                                    }
                                }
                            }
                        }
                    }
                }

                //sw.Close();

                Row header = new Row();
                header.RowIndex = 1;
                foreach (DataColumn column in dt.Columns)
                {
                    Cell headerCell = createTextCell(dt.Columns.IndexOf(column) + 1, //Excel's column starts from 1 instead of 0
                                                     1,
                                                     column.ColumnName);

                    header.AppendChild(headerCell);
                }
                data.AppendChild(header);


                IEnumerable<DataRow> results = dt.Rows.OfType<DataRow>().OrderByDescending(x => int.Parse(x[0].ToString()));
                int j = 0;
                foreach (DataRow row in results)
                {
                    data.AppendChild(createContentRow(row, j + 2));
                    j = j + 1;
                }

                /*
                DataRow contentRow;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    contentRow = dt.Rows[i];
                    // excel index starts from 1 and also data table already have one row which is header,
                    // that's why here we have "index + 2" as content row index
                    data.AppendChild(createContentRow(contentRow, i + 2));
                }
                 */

                worksheetPart.Worksheet.Append(data);
                // Append a new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "sheet1" };

                sheets.Append(sheet);

                workbookpart.Workbook.Save();

                // Close the document.
                spreadsheetDocument.Close();
            }
        }

        private Row createContentRow(System.Data.DataRow dataRow, int rowIndex)
        {
            Row row = new Row
            {
                RowIndex = (UInt32)rowIndex
            };

            for (int i = 0; i < dataRow.Table.Columns.Count; i++)
            {
                Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
                row.AppendChild(dataCell);
            }
            return row;
        }

        private Cell createTextCell(int columnIndex,
                                   int rowIndex,
                                   object cellValue)
        {
            Cell cell = new Cell();

            cell.DataType = CellValues.InlineString;

            // CellReference is the cell position, like 'A1', 'B30', where 'A' is the
            // column name and '1' is the row index.
            cell.CellReference = getColumnName(columnIndex) + rowIndex;

            InlineString inlineString = new InlineString();
            Text valueText = new Text();

            valueText.Text = cellValue.ToString();
            inlineString.AppendChild(valueText);
            cell.AppendChild(inlineString);

            return cell;
        }

        /// <summary>
        /// Convert the column index to the string name, for example column 27
        /// will return 'AA'
        /// </summary>
        /// <param name="columnIndex">Column Index</param>
        /// <returns>The string column name like 'AB'</returns>
        private string getColumnName(int columnIndex)
        {
            int dividend = columnIndex;
            string columnName = String.Empty;
            int subIndex;

            while (dividend > 0)
            {
                subIndex = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + subIndex).ToString() + columnName;
                dividend = (dividend - subIndex) / 26;
            }

            return columnName;
        }

    }
}
